<?php
	
	// set up database info
	$usr = "flytalk";
	$pwd = "ft1234";
	$db = $_GET['db'];
	if($db == ''){
		$db = "flytalk";
	}
	$host = "yangjustine.startlogicmysql.com";

	// establish connection
	$connection = mysql_connect($host,$usr,$pwd);
	if (!$connection) { echo("ERROR: " . mysql_error() . "\n"); }
	
	// dummy sql statements
//	$sql = 'SELECT * FROM `form`';
	//$sql =  "SELECT user.username, form.img, form.type, form.time, form.thread_id FROM `form`,`user` WHERE user.uid = form.from_uid";
// "INSERT INTO `IsFriend` (`username1`, `username2`,`dateAdded`) VALUES ('". $username1 ."', '". $username2 ."',CURRENT_TIMESTAMP);";
// $sql_recToFriend = "SELECT App.name, Recommendation.message FROM `FriendRec`,`App`, `Recommendation` WHERE FriendRec.sender = '". $username ."' and FriendRec.recid = Recommendation.recid and Recommendation.appid = App.appid;";
	
	
	// get method
	$method = $_GET['method'];
	$fid = $_GET['fid'];
	$from_uid = $_GET['uid'];
	$to_uid = $_GET['tid'];
	$text = $_GET['text'];
	$mine = $_GET['mine'];
	$thread = $_GET['thread'];
	$tp = $_GET['tp'];
	$price = $_GET['price'];
	$wait = $_GET['wait'];
	$recFood = $_GET['recFood'];
	$recRest = $_GET['recRest'];
	$food = $_GET['food'];
	
	if($method == 'getFlightPost' || $method == 'getFlightQuestion'){// get all posts from user's flight (TAP001) and sorted by time
		$sql = "SELECT DISTINCT form.thread_id FROM `form` , `user` WHERE form.from_uid = user.uid AND form.from_uid IN 
		(SELECT user.uid FROM `form` , `user` WHERE user.flight = 'TAP001') ORDER BY `time` DESC ";
		
	}else if($method == 'getTerminalPost' || $method == 'getTerminalQuestion'){ // get all posts from user's terminal (1) and sorted by time
		$sql = "SELECT DISTINCT form.thread_id FROM `form` , `user` WHERE form.from_uid = user.uid AND form.from_uid IN 
		(SELECT user.uid FROM `form` , `user` WHERE user.terminal = 1) ORDER BY `time` DESC ";
		
	}else if($method == 'getLangPost' || $method == 'getLangQuestion'){// get all posts from user's language (English) and sorted by time
		$sql = "SELECT DISTINCT form.thread_id FROM `form` , `user` WHERE form.from_uid = user.uid AND form.from_uid IN 
		(SELECT user.uid FROM `form` , `user` WHERE user.flight = 'TAP001') ORDER BY `time` DESC ";
		
	}else if($method == 'getRecentBoredPost'){// get recent suggestions
		$sql = "SELECT DISTINCT `thread_id` FROM `form` WHERE `type` = 'suggested' ORDER BY `time` DESC";
		
	}else if($method == 'getPopBoredPost'){// get popular suggestions
		$sql = "SELECT DISTINCT `thread_id` FROM `form` WHERE `type` = 'suggested' ORDER BY `up` DESC";
		
	}else if($method == 'getMyPost'){
		//$sql = "SELECT user.username, user.img, form.mine, form.fid, form.from_uid, form.to_uid, form.type, form.text, form.thread_id, form.touchpoint, form.price, form.wait, form.time, form.answered, form.up, form.down, form.followed, form.rated, form.recRest, form.recFood, form.food  FROM `form`,`user` WHERE user.uid = form.from_uid ORDER BY `time` DESC";
		$sql = "SELECT DISTINCT `thread_id` FROM `form` WHERE `mine` =1 ORDER BY `time` DESC";
	
	}else if($method == 'up'){// thumbs up a post
		$sql = "UPDATE `form` set form.up=1, form.rated = 1, form.mine = 1, form.followed = 4 where fid = ".$fid;
	
	}else if($method == 'down'){ // thumbs down a post
		$sql = "UPDATE `form` set form.down=1, form.rated = 1, form.mine = 0, form.followed = 5 where fid = ".$fid;
	
	}else if($method == 'follow'){ // follow a post
		$sql = "UPDATE `form` set form.followed=3, form.mine = 1 where fid = ".$fid;
	
	}else if($method == 'unfollow'){ // unfollow a post
		$sql = "UPDATE `form` set form.followed=0, form.mine = 0 where fid = ".$fid;
	
	}else if($method == 'answered'){ // answer a post
		$sql = "UPDATE `form` set form.answered='yes' where fid = ".$fid;
	
	}else if($method == 'getPost'){
		$sql = "SELECT form.mine, form.fid, form.from_uid, form.to_uid, form.type, form.text, form.thread_id, form.touchpoint, form.price, form.wait, form.time, form.answered, form.up, form.down, form.followed, form.rated, form.recRest, form.recFood, form.food  FROM `form`,`user` WHERE form.fid = ".$fid;
	
	}else if($method == 'saveComment'){
		$sql = "INSERT INTO `form` ( `fid` , `from_uid` , `to_uid` , `type` , `text` , `thread_id` , `touchpoint` , `price` , `wait` , `time` , `answered` , `up` , `down` , `rated` , `followed` , `recRest` , `recFood` , `food` , `mine` )
		VALUES (NULL , '".$from_uid."', '', 'commented', '".$text."', '', '', '', '',".time()." , '', '0', '0', '0', '1', '0', '0', '', '".$mine."');";
	
	}else if($method == 'saveQuestion'){
		$sql = "INSERT INTO `form` ( `fid` , `from_uid` , `to_uid` , `type` , `text` , `thread_id` , `touchpoint` , `price` , `wait` , `time` , `answered` , `up` , `down` , `rated` , `followed` , `recRest` , `recFood` , `food` , `mine` )
		VALUES (NULL , '".$from_uid."', '', 'asked', '".$text."', '', '', '', '',".time()." , '', '0', '0', '0', '1', '0', '0', '', '".$mine."');";
	
	}else if($method == 'saveReply'){
		$sql = "INSERT INTO `form` ( `fid` , `from_uid` , `to_uid` , `type` , `text` , `thread_id` , `touchpoint` , `price` , `wait` , `time` , `answered` , `up` , `down` , `rated` , `followed` , `recRest` , `recFood` , `food` , `mine` )
		VALUES (NULL , '".$from_uid."', '".$to_uid."', 'replied', '".$text."', '".$thread."', '', '', '',".time()." , '', '0', '0', '0', '".$mine."', '0', '0', '', '".$mine."');";
	
	}else if($method == 'saveSuggestion'){
		$sql = "INSERT INTO `form` ( `fid` , `from_uid` , `to_uid` , `type` , `text` , `thread_id` , `touchpoint` , `price` , `wait` , `time` , `answered` , `up` , `down` , `rated` , `followed` , `recRest` , `recFood` , `food` , `mine` )
		VALUES (NULL , '".$from_uid."', '', 'suggested', '".$text."', '', '', '', '',".time()." , '', '0', '0', '0', '1', '0', '0', '', '".$mine."')";
	
	}else if($method == 'saveReview'){
		// rephrase comment
		$phrase;
		
		if($recRest == 1 && $recFood == 1){
				$phrase =  $tp. " and recommended the place as well as the ".$food.". ".$text;
		}else if($recRest == 1){
				$phrase =  $tp. " and recommended the place. ".$text;
		}else if($recFood == 1){
				$phrase =  $tp. " and recommended the ".$food.". ".$text;
		}else{
			    $phrase =  $tp. " ".$text;
		}
						
		$sql = "INSERT INTO `form` ( `fid` , `from_uid` , `to_uid` , `type` , `text` , `thread_id` , `touchpoint` , `price` , `wait` , `time` , `answered` , `up` , `down` , `rated` , `followed` , `recRest` , `recFood` , `food` , `mine` )
		VALUES (NULL , '".$from_uid."', '', 'reviewed', '".$phrase."', '', '".$tp."', '".$price."', '".$wait."',".time()." , '', '0', '0', '0', '1', '".$recRest."', '".$recFood."', '".$food."', '".$mine."')";
	}else if($method == 'getNotification'){
		$sql = "SELECT * from `note` where `id` < 11 ORDER BY `id` ASC ";
	}else if($method == 'getNewNote'){
		$sql = "SELECT * from `note` where `read` = 'unread'";
	}else if($method == 'markNoteAsOld'){
		$sql = "UPDATE `note` set `read`='read' where id = ".$fid;
	}
	
    $posts = mysql_db_query($db,"$sql",$connection);
	$post_list = array();
	if (!$posts) {
    		echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
	}
	else{
		 
		if($method == 'down' || $method == 'follow' || $method == 'unfollow' || $method == 'answered'|| $method == 'markNoteAsOld' ){
		}else if($method == 'saveQuestion' || $method == 'saveComment' || $method == 'saveSuggestion' || $method == 'saveReview'){
			$lastid = mysql_insert_id();
			$sql = "UPDATE `form` set form.thread_id='".$lastid."' where fid = ".$lastid;
			$result = mysql_db_query($db,"$sql",$connection);
			if (!$result) {
    			echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
			}
		}else if($method == 'saveReply'){
			// make sure original thread is marked as answered (or stack)
			$sql = "UPDATE `form` set form.answered='yes', form.mine = 1 where fid = ".$thread;
			$result = mysql_db_query($db,"$sql",$connection);
			if (!$result) {
    			echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
			}
	
			if($from_uid != 3 && $to_uid == 3){   // if another user is replying to happy travler, add a notification
							
				$sql = "SELECT * from `user` where uid = ".$from_uid;
				$result = mysql_db_query($db,"$sql",$connection);
				if (!$result) {
    				echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
				}else{
					$row = mysql_fetch_array($result);
					$sql = "INSERT INTO `note` ( `id` , `uid` , `thread_id` , `type` , `text` ) VALUES (NULL , '".$to_uid."', '".$thread."', 'replied', '".$row['firstname']." replied to your post.');";
					$result = mysql_db_query($db,"$sql",$connection);
					if (!$result) {
						echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
					}
				}
			}else{
				$sql = "SELECT * from `form` where `fid` = ".$thread;
				$result = mysql_db_query($db,"$sql",$connection);
				$row = mysql_fetch_array($result);

				if($row['mine'] == 1){
					$sql = "SELECT * from `user` where uid = ".$from_uid;
					$result = mysql_db_query($db,"$sql",$connection);
					if (!$result) {
    					echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
					}else{
						$row = mysql_fetch_array($result);
						$sql = "INSERT INTO `note` ( `id` , `uid` , `thread_id` , `type` , `text` ) VALUES (NULL , '".$to_uid."', '".$thread."', 'replied', '".$row['firstname']." replied to your post.');";
						$result = mysql_db_query($db,"$sql",$connection);
						if (!$result) {
							echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
						}
					}
				}	
			}
			
				
		}else if($method == 'up'){
			// create notification if liked a post by the main user		
			
		}else if($method == 'getNotification' || $method == 'getNewNote'){
			$index = 0;
			while($row = mysql_fetch_array($posts))
			{
				/*
				if($index == 0){
					 $curpost = array(
                			'id' => "",
							'uid' => "",
							'thread_id' => "",
							'type' => "title",
							'text' => "Official Notifications",
							'read' => "title"
							); 
					 $post_list[] = $curpost;
				}
						
				if($index == 1){
					 $curpost = array(
                			'id' => "",
							'uid' => "",
							'thread_id' => "",
							'type' => "title",
							'text' => "Help Out",
							'read' => "title"
							); 
					 $post_list[] = $curpost;
				}*/
						
				// get values for current post
				 $curpost = array(
                'id' => $row['id'],
				'uid' => $row['uid'],
				'thread_id' => $row['thread_id'],
				'type' => $row['type'],
				'text' => $row['text'],
				'read' => $row['read'],
				); 
				if($curpost['read'] == 'announce'){
					$curpost['read'] = 'announcement';	
				}
				$post_list[] = $curpost;
				$index++;
			}
			
			if($method == 'getNotification'){
				// unread
				$sql = "SELECT * from `note` where `id` > 10 ORDER BY `id` DESC ";
				$posts = mysql_db_query($db,"$sql",$connection);
				$index = 0;
				if (!$posts) {
    				echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
				}else{
					while($row = mysql_fetch_array($posts))
					{
						/*
						if($index == 0){
							 $curpost = array(
                			'id' => "",
							'uid' => "",
							'thread_id' => "",
							'type' => "title",
							'text' => "Recent Notifications",
							'read' => "title"
							); 
							$post_list[] = $curpost;
						}*/
						
						// get values for current post
						 $curpost = array(
                		'id' => $row['id'],
						'uid' => $row['uid'],
						'thread_id' => $row['thread_id'],
						'type' => $row['type'],
						'text' => $row['text'],
						'read' => $row['read'],
						); 
						if($curpost['read'] == 'read' || $curpost['read'] == 'read' ){
							$curpost['read'] = 'notification';	
						}
						$post_list[] = $curpost;
						$index++;
					}
				}
				
				$sql = "UPDATE `note` set `read`='read' where `read` = 'unread'";
				$posts = mysql_db_query($db,"$sql",$connection);
				if (!$posts) {
    				echo("ERROR: " . mysql_error() . "\n$SQL\n"); 	
				}
			}
		}else{ // getMixedPosts
			// for each thread, get either the question or question and last post if following
			while($row1 = mysql_fetch_array($posts))
			{	
				$sql1 = "SELECT user.username, user.img, form.mine, form.fid, form.from_uid, form.to_uid, form.type, form.text, form.thread_id, form.touchpoint, form.price, form.wait, form.time, form.answered, form.up, form.down, form.followed, form.rated, form.recRest, form.recFood, form.food  
				FROM `form`,`user` WHERE user.uid = form.from_uid and form.thread_id =".$row1['thread_id']." ORDER BY `time` ASC";
	
				$results = mysql_db_query($db,"$sql1",$connection);
	
				$curpost = "";
				$index = 0;
				$size = mysql_num_rows($results)-1;
				while($row = mysql_fetch_array($results))
				{
					// only get post and add to the queue if it's the first or last of array
					if($index == 0 || $index == $size){
						// get values for current post
			 			$curpost = array(
               			'fid' => $row['fid'],
						'from_user' => $row['username'],
						'mine' => $row['mine'],
						'from_uid' => $row['from_uid'],
						'to_uid' => $row['to_uid'],
						'image_url' => $row['img'],
						'type' => $row['type'],
						'text' => $row['text'],
						'thread' => $row['thread_id'],
						'tp' => $row['touchpoint'],
						'price' => $row['price'],
						'wait' => $row['wait'],
						'date' => $row['time'],
                		'answered' => $row['answered'],
						'up' => $row['up'],
                		'down' => $row['down'],
                		'rated' => $row['rated'],
						'followed' => $row['followed'],
						'recRest' => $row['recRest'],
						'recFood' => $row['recFood'],
						'food' => $row['food'],
            			); 
						
						if(($method == 'getFlightQuestion' || $method == 'getTerminalQuestion' || $method == 'getLangQuestion') && $row['type'] != 'asked'){}
						else{
						// update time to time ago
							$granularity = 1;
							$format = 'Y-m-d H:i:s';
							$timestamp = $curpost['date'];
			
							$difference = time() - $timestamp;
							if($difference < 0) $curpost['date'] = '0 seconds ago';
							else if($difference < 864000){
								$periods = array ('week' => 604800, 'day' => 86400, 'hr' => 3600, 'min' => 60, 'sec' => 1);
								$output = '';
								foreach($periods as $key => $value){
									if($difference >= $value && $granularity > 0){
										$time = round($difference/$value);
										$difference %= $value;
										$output .= ($output ? ' ' : '').$time.' ';
										$output .= (($time > 1 && $key == 'day') ? $key.'s' : $key);
										$granularity--;
									}
								}
								$curpost['date'] = ($output ? $output : '0 seconds').' ago';
							}else $curpost['date'] = date($format, $timestamp);
							
							if($curpost['followed'] == 1){
								$curpost['followed'] = "[you wrote this post]";
							}else if($curpost['followed'] == 2){
								$curpost['followed'] = "[You replied to this thread]";
							}else if($curpost['followed'] == 3){
								$curpost['followed'] = "[You followed this post]";
							}else if($curpost['followed'] == 4){
								$curpost['followed'] = "[You liked this post]";
							}else if($curpost['followed'] == 5){
								$curpost['followed'] = "[You disliked this post]";
							}else{
								$curpost['followed'] = " ";
							}
							
							// add post to array
							$post_list[] = $curpost;
						}	
					} // end of if	
					$index++;			
				} // end of while	     
			}// end of while1
		}// end of else

		$callback = $_GET['callback'];

		if ($callback) {
    		header('Content-Type: text/javascript');
    		echo $callback . '({"results":' . json_encode($post_list) . '});';
			//echo $callback . '({"results":' . $sql. '});';
		} else {
    		header('Content-Type: application/x-json');
    		echo json_encode($post_list);
		}
	}

	mysql_close($connection);

?>